package CelestialDataAccess;

import javax.sql.rowset.*; // provides cached row set interface
import java.sql.*;         // provides connection object and sql exception
import java.text.SimpleDateFormat;
import java.util.Dictionary;
import java.util.List;
import java.util.ArrayList; // for test routine only
import java.util.Iterator;  // for test routine only

public class EmployeesDataAccess extends DataAccessBase{

    private final static SimpleDateFormat sdf =
            new SimpleDateFormat("yyyy-MM-dd");

    public static final String EMP_ID = "EmployeeID";
    public static final String PASSWORD = "Password";
    public static final String USR_LEVEL = "UserLevel";
    public static final String EMP_ON = "EmployedOn";

    public static final String FIRST_NAME = "FirstName";
    public static final String LAST_NAME = "LastName";
    public static final String CONTACT = "Contact";
    public static final String EMAIL = "Email";
    public static final String DOB = "DOB";
    public static final String ADDR_L1 = "AddressLine1";
    public static final String ADDR_L2 = "AddressLine2";
    public static final String POSTAL = "PostalCode";
    
    public static final String IS_ACTIVE = "IsActive";

    /*************************************************************************/
    /* get/set methods */
    public SearchCriteria getSearchCriteria(){ return new EmployeesSearchCriteria();}
    
    /*************************************************************************/
    /* Client methods */
    public int insertEmployee(Employee emp) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspInsertEmployee(?,?,?,?,?,?,?,?,?,?,?,?,)}");
        try{
            stm.setBytes(PASSWORD, CryptService.encrypt(emp.getPassword()));
            stm.setInt(USR_LEVEL, emp.getUserLevel());
            //Employee ID: IDENTITY
            stm.setDate(EMP_ON, java.sql.Date.valueOf(sdf.format(emp.getEmploymentDate())));

            stm.setString(FIRST_NAME, emp.getFirstName());
            stm.setString(LAST_NAME, emp.getLastName());
            stm.setString(CONTACT, emp.getContact());
            stm.setString(EMAIL, emp.getEmail());
            stm.setDate(DOB, java.sql.Date.valueOf(sdf.format(emp.getDOB())));

            stm.setString(ADDR_L1, emp.getAddressLine1());
            stm.setString(ADDR_L2, emp.getAddressLine1());
            stm.setString(POSTAL, emp.getPostalCode());

            stm.setBoolean(IS_ACTIVE, emp.isActive());

            stm.execute();
            
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public int updateEmployee(Employee emp) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspUpdateEmployee(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        try{
            stm.setBytes(PASSWORD, CryptService.encrypt(emp.getPassword()));
            stm.setInt(USR_LEVEL, emp.getUserLevel());
            stm.setInt(EMP_ID,emp.getEmployeeID());
            stm.setDate(EMP_ON, java.sql.Date.valueOf(sdf.format(emp.getEmploymentDate())));

            stm.setString(FIRST_NAME, emp.getFirstName());
            stm.setString(LAST_NAME, emp.getLastName());
            stm.setString(CONTACT, emp.getContact());
            stm.setString(EMAIL, emp.getEmail());
            stm.setDate(DOB, java.sql.Date.valueOf(sdf.format(emp.getDOB())));

            stm.setString(ADDR_L1, emp.getAddressLine1());
            stm.setString(ADDR_L2, emp.getAddressLine2());
            stm.setString(POSTAL, emp.getPostalCode());

            stm.setBoolean(IS_ACTIVE, emp.isActive());

            stm.execute();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public int deleteEmployee(int emp_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspDeletetEmployee(?)}");
        try{
            stm.setInt(EMP_ID, emp_id);
            stm.execute();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public Employee getEmployeeByID(int emp_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{Call uspGetEmployeeByID(?)}");
        CachedRowSet crs;

        try{
            stm.setInt(EMP_ID, emp_id);
            crs = getData(stm);
            
            crs.last();
            if(crs.getRow()==0) return null;

            return Employee.createEmployee(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public Employee getEmployeeByEmail(String email) throws DataAccessException{

        CallableStatement stm = prepareStatement("{Call uspGetEmployeeByEmail(?)}");
        CachedRowSet crs;

        try{
            stm.setString(EMAIL, email);
            crs = getData(stm);

            crs.last();
            if(crs.getRow()==0) return null;

            return Employee.createEmployee(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public static int getNextEmployeeID() throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetNextEmployeeID}");
        CachedRowSet crs = getData(stm);

        try{
            crs.last();
            return crs.getInt(1); // returns scalar
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public void fill(Dictionary<Integer, Employee> dic) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetEmployees()}");
        DataAccessBase.fill(Employee.getFactory(), dic, stm);
    }
    /*----------------------------------------------------------------------*/
    public void fill(List<Employee> lst) throws DataAccessException{
        
        CallableStatement stm = prepareStatement("{CALL uspGetEmployees()}");
        DataAccessBase.fill(Employee.getFactory(), lst, stm);
    }
    /*----------------------------------------------------------------------*/
    void searchEmployees(String search_sql, List<Employee> lst) throws DataAccessException{

        CallableStatement stm = prepareStatement(search_sql);
        DataAccessBase.fill(Employee.getFactory(), lst, stm);
    }
    /*----------------------------------------------------------------------*/
    public void searchEmployees(SearchCriteria criteria, List<Employee> lst) throws DataAccessException{

        /* don't build search string if "non-employee" criteria is passed */
        if(criteria.getClass() != EmployeesSearchCriteria.class) return;

        CallableStatement stm = prepareStatement(criteria.buildSQL());
        DataAccessBase.fill(Employee.getFactory(), lst, stm);
    }
    /*----------------------------------------------------------------------*/
    public void getAddedToday(List<Employee> lst) throws DataAccessException{
        CallableStatement stm = prepareStatement("{CALL uspGetAddedTodayEmployees}");
        fill(Employee.getFactory(), lst, stm);
    }
    /************************************************************************/
    /* Helpers */

    /*************************************************************************/
    /* Test */
    public static void main(String[] args){

        Employee emp1, emp2;
        EmployeesDataAccess daEmployees;

        try{
           //DataAccessBase.open();
           daEmployees = new EmployeesDataAccess();

           /* test getEmployee */
           emp2 = daEmployees.getEmployeeByID(1000);
           System.out.println(emp2.getEmployeeID());

           /* test getNextEmployeeID */
           System.out.println(getNextEmployeeID());

           /* test search */
           List<Employee> lst = new ArrayList<Employee>();

           SearchCriteria criteria = daEmployees.getSearchCriteria();
           //criteria.setValue("LastName", "Chan");
           criteria.setValue("Email", "E_chseng");
           criteria.setOperator("Email", SearchCriteria.LIKE);

           daEmployees.searchEmployees(criteria, lst);
           Employee emp;
           Iterator<Employee> emp_itr = lst.iterator();
           while(emp_itr.hasNext()){
               emp = emp_itr.next();
               emp.setPassword("1123581321");
               emp.setPostalCode("570023");
               emp.setAddressLine2("#02-03");
               daEmployees.updateEmployee(emp);
               System.out.println(daEmployees.getEmployeeByID(1000).toString(true));
           }
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            System.out.println(ex.toString());
            return;
        }
    }
    /*************************************************************************/
    /* Search Criteria class*/
    private class EmployeesSearchCriteria extends SearchCriteria{

        /*********************************************************************/
        /* Constructors */
        EmployeesSearchCriteria(){

            m_strSelectClause = "SELECT * FROM tblEmployees";

            m_mapValues.put(EMP_ID, null);
            m_mapValues.put(FIRST_NAME, null);
            m_mapValues.put(LAST_NAME, null);
            m_mapValues.put(IS_ACTIVE, null);
            m_mapValues.put(EMP_ON, null);
            m_mapValues.put(USR_LEVEL, null);
            m_mapValues.put(EMAIL, null);

            m_mapOperators.put(EMP_ID, SearchCriteria.EQU);
            m_mapOperators.put(FIRST_NAME, SearchCriteria.EQU);
            m_mapOperators.put(LAST_NAME, SearchCriteria.EQU);
            m_mapOperators.put(IS_ACTIVE, SearchCriteria.EQU);
            m_mapOperators.put(EMP_ON, SearchCriteria.EQU);
            m_mapOperators.put(USR_LEVEL, SearchCriteria.EQU);
            m_mapOperators.put(EMAIL, SearchCriteria.EQU);
        }
        /*********************************************************************/
    }
}
